package com.guanyong.dao;

import com.guanyong.model.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * Created by mxk on 2017/5/26.
 * 分享日志
 */
public interface KeyBaseDaoMapper {

    @Select("SELECT * FROM key_base")
    @ResultMap("KeyBaseResultMap")
    List<KeyBaseModel> selectByAll();


    @Select("SELECT * FROM key_weibo_hotword,key_base where key_weibo_hotword.base_id=key_base.id ORDER BY cur_date desc")
    @ResultMap("KeyWeiboZhishuResultMap")
    List<KeyWeiboZhishuModel> getWeiboZhishuAll();

    @Select("SELECT  key_douban_base.base_id, key_douban_base.score, b.maoyan_score,key_base.title " +
            "FROM (key_douban_base,key_base)  left JOIN (SELECT * FROM key_maoyan_base WHERE key_maoyan_base.cur_date=#{dateStr} ) b   ON key_douban_base.base_id= b.base_id\n" +
            "WHERE key_douban_base.cur_date=#{dateStr} AND key_base.id=key_douban_base.base_id " +
            "ORDER BY key_douban_base.score DESC LIMIT 7")
    @ResultMap("KeyPingfenResultMap")
    List<KeyPingfenModel> getKeyPingfenByDay(@Param("dateStr") String dateStr);



    @Select("SELECT * \n" +
            "FROM key_weibo_official_detail \n" +
            "where key_weibo_official_detail.create_time>=#{dateStr} \n" +
            "group by key_weibo_official_detail.title \n" +
            "ORDER BY share_num DESC LIMIT 5 ")
    @ResultMap("KeyBozhuResultMap")
    List<KeyBozhuModel> getBozhuTop5(@Param("dateStr")String nowDate);

    @Select("SELECT * FROM key_weibo_topic_url WHERE cur_date=#{dateStr} AND yes_or_not=1 ")
    @ResultMap("KeyHuatiResultMap")
    List<KeyHuatiModel> getHuatiList(@Param("dateStr") String dateStr);

    @Select("SELECT key_weibo_hotword.* FROM key_weibo_hotword,key_base \n" +
            "where key_weibo_hotword.base_id=key_base.id and key_weibo_hotword.base_id=#{id}\n" +
            "ORDER BY cur_date desc\n" +
            "LIMIT 30")
    @ResultMap("KeyWeiboZhishuResultMap")
    List<KeyWeiboZhishuModel> getWeiboZhishuById(@Param("id")int id);


    @Select("SELECT key_douban_base.base_id, key_douban_base.score, b.maoyan_score,key_base.title\n" +
            "FROM (key_douban_base,key_base)  left JOIN (SELECT * FROM key_maoyan_base WHERE key_maoyan_base.create_time>=#{dateStr} AND key_maoyan_base.base_id=#{id} ) b   ON key_douban_base.base_id= b.base_id\n" +
            "WHERE key_douban_base.create_time>=#{dateStr} AND key_douban_base.base_id=#{id} AND key_base.id=key_douban_base.base_id ORDER BY key_douban_base.create_time DESC")
    @ResultMap("KeyPingfenResultMap")
    List<KeyPingfenModel> getKeyPingfenByIdDay(@Param("id")int id, @Param("dateStr")String dateStr);


    @Select("SELECT *\n" +
            "FROM key_weibo_official_detail,key_weibo_official_url\n" +
            "WHERE key_weibo_official_url.id=key_weibo_official_detail.official_id AND key_weibo_official_url.base_id=#{id} AND key_weibo_official_detail.create_time>=#{dateStr} AND key_weibo_official_url.create_time>=#{dateStr}\n" +
            "group by key_weibo_official_detail.title " +
            "ORDER BY key_weibo_official_detail.share_num DESC LIMIT 5")
    @ResultMap("KeyBozhuResultMap")
    List<KeyBozhuModel> getBozhuTop5ById(@Param("id") int id, @Param("dateStr")String nowDate);


    @Select("SELECT * FROM key_weibo_official_url WHERE base_id=#{id} AND create_time>=#{dateStr} ORDER BY share_num DESC")
    @ResultMap("KeyWeiboResultMap")
    List<KeyWeiboModel> getWeiboByIdDay(@Param("id")int id, @Param("dateStr")String dateStr);


    @Select("SELECT * FROM key_weibo_topic_url WHERE cur_date>=#{dateStr} and base_id=#{id} AND yes_or_not=1")
    @ResultMap("KeyHuatiResultMap")
    List<KeyHuatiModel> getHuatiByIdList(@Param("id")int id, @Param("dateStr")String dateStr);


    @Select("SELECT * FROM key_maoyan_base WHERE base_id=#{id} ORDER BY cur_date LIMIT 9,9")
    @ResultMap("KeyFilmResultMap")
    List<KeyFilmModel> getKeyFilmList(@Param("id")int id);

    @Select("SELECT first_play  FROM key_base WHERE id=#{id}")
    String getFirtPlay(@Param("id")int id);

//    @Select("SELECT time, SUM(maoyan_box) maoyan , SUM(nuomi_box) nuomi  \n" +
//            "FROM key_movie_box \n" +
//            "WHERE time in (${dateStrList})\n" +
//            "GROUP BY time")
//    @ResultMap("KeyPiaofangResultMap")
//    List<KeyPiaofangModel> getLodPiaofang(@Param("dateStrList")String dateStrList);

    @Select("SELECT time, SUM(maoyan_box) maoyan , SUM(nuomi_box) nuomi  \n" +
            "FROM key_movie_box \n" +
            "WHERE time LIKE '%23:59:59' \n" +
            "GROUP BY time \n" +
            "LIMIT 30")
    @ResultMap("KeyPiaofangResultMap")
    List<KeyPiaofangModel> getLodPiaofang();

    @Select("SELECT b.time ,SUM(b.n) nuomi,SUM(b.m) maoyan FROM (SELECT MAX(time) time , MAX(maoyan_box) m , MAX(nuomi_box) n  \n" +
            "FROM key_movie_box \n" +
            "WHERE time>#{dateStr} \n" +
            "GROUP BY base_id) b")
    @ResultMap("KeyPiaofangResultMap")
    KeyPiaofangModel getNowPiaofang(@Param("dateStr")String dateStr);


    @Select("SELECT  time,nuomi_box nuomi,maoyan_box maoyan\n" +
            "FROM key_movie_box \n" +
            "WHERE time>#{dateStr} AND base_id=#{id}\n" +
            "ORDER BY time DESC\n" +
            "LIMIT 1")
    @ResultMap("KeyPiaofangResultMap")
    KeyPiaofangModel getNowPiaofangById(@Param("id")int id, @Param("dateStr")String dateStr);

//    @Select("SELECT time, SUM(maoyan_box) maoyan , SUM(nuomi_box) nuomi  \n" +
//            "FROM key_movie_box \n" +
//            "WHERE time in (${dateStrList}) and base_id=${id}\n" +
//            "GROUP BY time")
//    @ResultMap("KeyPiaofangResultMap")
//    List<KeyPiaofangModel> getLodPiaofangById(@Param("id")int id,@Param("dateStrList")String dateStrList);


    @Select("SELECT time, SUM(maoyan_box) maoyan , SUM(nuomi_box) nuomi  \n" +
            "FROM key_movie_box \n" +
            "WHERE time LIKE '%23:59:59' and base_id=${id}\n" +
            "GROUP BY time \n" +
            "ORDER BY time DESC\n" +
            "LIMIT 30")
    @ResultMap("KeyPiaofangResultMap")
    List<KeyPiaofangModel> getLodPiaofangById(@Param("id")int id);

    @Select("SELECT time, SUM(maoyan_box) maoyan , SUM(nuomi_box) nuomi  \n" +
            "FROM key_movie_box \n" +
            "WHERE time LIKE '%23:59:59' and base_id=${id}\n" +
            "GROUP BY time \n" +
            "ORDER BY time DESC\n" +
            "LIMIT 1")
    @ResultMap("KeyPiaofangResultMap")
    List<KeyPiaofangModel> getPiaofangById(@Param("id")int id);


    @Select("SELECT a.type,b.cur_date,SUM(b.play_count) count \n" +
            "FROM key_video_base a LEFT JOIN key_video_detail b ON  a.url_md5=b.url_md5\n" +
            "WHERE  a.base_id=#{id} AND b.cur_date =#{dateStr} and a.type in ('2','3','4') \n" +
            "GROUP BY type")
    @ResultMap("KeyPlayResultMap")
    List<KeyPlayModel> getPlayCountById(@Param("id")int id, @Param("dateStr")String dateStr);

    @Select("SELECT k.title, a.base_id, a.type,b.cur_date,SUM(b.play_count) count \n" +
            "FROM (key_base k, key_video_base a) LEFT JOIN key_video_detail b ON  a.url_md5=b.url_md5\n" +
            "WHERE   b.cur_date =#{dateStr} and a.type in ('2','3','4') AND k.id=a.base_id\n" +
            "GROUP BY a.base_id ,a.type\n")
    @ResultMap("KeyPlay2ResultMap")
    List<KeyPlay2Model> getPlayCountAll(@Param("dateStr")String dateStr);




    @Update("UPDATE key_weibo_topic_url SET yes_or_not=1 \n" +
            "WHERE yes_or_not=0 AND url_md5 not in (${list})")
    Integer upHuati(@Param("list")String list);
}
